﻿/*----------------------------------------------------------------
    // Copyright (C) 2009 江苏华招网
    // 版权所有。 
    // 作者：庄金冬 
    //
    // 文件名：DAL_Company.cs
    // 文件功能描述：企业数据访问逻辑
    // 创建标识：庄金冬2009-03-06
    //
    // 修改标识：
    // 修改描述：
    //
    // 修改标识：
    // 修改描述：
    //
    // 审核人：
    // 审核时间：
    // 审核说明：
 //----------------------------------------------------------------*/
using System;
using System.Collections.Generic;

using System.Text;
using HZ.DrugTrade.Model;
using HZ.Common;
using System.Data;
using HZ.Data;
using System.Data.SqlClient;
using Oracle.DataAccess.Client;

namespace HZ.DrugTrade.DAL
{
    /// <summary>
    /// 企业数据访问逻辑
    /// </summary>
    public class DAL_Company
    {
        /// <summary>
        /// 获取所有企业
        /// </summary>
        /// <returns>所有企业</returns>
        public static IList<Company> GetAllCompany()
        {
            string sql_GetAllCompany = @"SELECT * FROM TB_Company ";
            return HZ.Data.DAL_Helper.GetModelList<Company>(sql_GetAllCompany, null);
        }

        /// <summary>
        /// 获取配送企业数量
        /// </summary>
        /// <returns>数量</returns>
        public static int GetAllCompanyCount()
        {
            string sql_GetAllCompany = @"SELECT * FROM TB_Company  where CompanyType=2";
            return HZ.Data.DAL_Helper.GetModelList<Company>(sql_GetAllCompany, null).Count;
        }
        /// <summary>
        /// 获取生产企业最大编号    add by tzh
        /// </summary>
        /// <returns></returns>
        public static string GetCompanySID()
        {
            string sql_GetCompanyID = @"SELECT MAX(COMPANYID) from TB_COMPANYINFO where COMPANYID like '%QS%' and COMPANYID  not like '%SJ%'";
            return HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql_GetCompanyID).ToString();
           
        }
        /// <summary>
        /// 获取经营企业最大编号    add by tzh
        /// </summary>
        /// <returns></returns>
        public static string GetCompanyJID()
        {
            string sql_GetCompanyJID = @"SELECT MAX(COMPANYID) from TB_COMPANYINFO where COMPANYID like '%QJ%' and COMPANYID  not like '%SJ%'";
            return HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql_GetCompanyJID).ToString();
             
        }
        /// <summary>
        /// 获取配送企业最大编号    add by tzh
        /// </summary>
        /// <returns></returns>
        public static string GetCompanyPID()
        {
            string sql_GetCompanyPID = @"SELECT MAX(COMPANYID) from TB_COMPANYINFO where COMPANYID <'QP9000' and COMPANYID like '%QP%'";
            return HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql_GetCompanyPID).ToString();
          
        }
        /// <summary>
        /// 编辑企业
        /// </summary>
        /// <returns>True:成功;False:失败</returns>
        public static bool UpdateCompany(Company company)
        {
            int retValue = 0;
            HZ.Data.IDbHelper h = HZ.Data.DbHelperFactory.Create();
            try
            {

                h.BeginTrans();

                List<string> UpdateCompanyparasnames = new List<string>();
                List<object> UpdateCompanyvalues = new List<object>();

                UpdateCompanyparasnames.Add("@in_CompanyID");
                UpdateCompanyvalues.Add(string.IsNullOrEmpty(company.CompanyID) ? "" : company.CompanyID);

                UpdateCompanyparasnames.Add("@in_CompanyName");
                UpdateCompanyvalues.Add(string.IsNullOrEmpty(company.CompanyName) ? "" : company.CompanyName);

                UpdateCompanyparasnames.Add("@in_CompanyType");
                UpdateCompanyvalues.Add(company.CompanyType == null ? 1 : (int)company.CompanyType);

                string sqlUpdateCompany = @"UPDATE TB_Company SET 
                                                CompanyName = @in_CompanyName,CompanyType = @in_CompanyType	
		                                                WHERE CompanyID = @in_CompanyID";

                h.ExecuteNonQuery(CommandType.Text, sqlUpdateCompany, HZ.Data.DBParameterFactory.GetIDbDataParameterList(UpdateCompanyparasnames, UpdateCompanyvalues).ToArray());

                #region 前
                //SqlParameter[] paras = new SqlParameter[4];

                ////TB_Company【CompanyID,CompanyName_PS,IsUsing】
                ////企业编号
                //paras[0] = new SqlParameter("@in_CompanyID", string.IsNullOrEmpty(company.CompanyID) ? "" : company.CompanyID);
                ////企业名称
                //paras[1] = new SqlParameter("@in_CompanyName", string.IsNullOrEmpty(company.CompanyName) ? "" : company.CompanyName);
                ////企业类型 
                //paras[2] = new SqlParameter("@in_CompanyType", company.CompanyType == null ? 1 : (int)company.CompanyType);


                ////输入参数处理结果(0,添加失败;1,添加成功;)
                //paras[3] = new SqlParameter("@out_success", SqlDbType.Int);
                //paras[3].Direction = ParameterDirection.Output;

                ////执行存储过程
                //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_Company_Update", paras);

                //retValue = int.Parse(paras[3].Value.ToString()); 
                #endregion

                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_Company).FullName, "更新企业信息不成功", ex);

            }

            return retValue == 1;
        }
        /// <summary>
        /// 编辑企业
        /// </summary>
        /// <returns>True:成功;False:失败</returns>
        public static int UpdateCompany2(Company company)
        {
            int result = 0;

            var sh = HZ.Data.DbHelperFactory.Create();
            try
            {
                sh.BeginTrans();

                #region 编辑配送企业

                string strExists = string.Format(@"SELECT * FROM TB_CompanyInfo where TB_CompanyInfo.CompanyName='{0}' and TB_CompanyInfo.CompanyId<>'{1}' ", company.CompanyName, company.CompanyID);

                result = sh.ExecuteDataSet(strExists).Tables[0].Rows.Count;
                //检验存在性
                if (result > 0)
                {
                    sh.RollTrans();
                    return 2;
                }

                #endregion

                #region 临时的
                string strSql = string.Format(@"UPDATE TB_CompanyInfo SET  CompanyName = '{0}' WHERE CompanyID = '{1}'", company.CompanyName, company.CompanyID);

                strSql += string.Format(@";UPDATE TB_Company SET  CompanyName = '{0}' WHERE CompanyID = '{1}'", company.CompanyName, company.CompanyID);

                strSql += string.Format(@";UPDATE TB_User SET  UserName = '{0}' WHERE UserID = '{1}'", company.CompanyName, company.CompanyID);

                #region 商品表中的企业名称名称
                strSql += string.Format(@";UPDATE TB_Goods SET  CompanyName_SC = '{0}' WHERE CompanyID_SC = '{1}'", company.CompanyName, company.CompanyID);

                strSql += string.Format(@";UPDATE TB_Goods SET  CompanyName_TB = '{0}' WHERE CompanyID_TB = '{1}';", company.CompanyName, company.CompanyID);
                #endregion

                sh.ExecuteNonQuery("begin  " + strSql + "   end;");

                #endregion

                sh.CommitTrans();
                return 1;

            }
            catch (Exception ex)
            {
                sh.RollTrans();
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_Company).FullName, "更新企业信息不成功", ex);
                return 0;

            }
        }
        /// <summary>
        /// 删除企业
        /// </summary>
        /// <param name="company">企业</param>
        /// <returns>0.删除失败;1.删除成功;2.已被使用不能删除;</returns>
        public static int DeleteCompany(Company company)
        {

            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[2];

                //TB_Company【CompanyID,IsUsing】
                //企业编号
                paras[0] = new SqlParameter("@in_CompanyID", string.IsNullOrEmpty(company.CompanyID) ? "" : company.CompanyID);

                //输入参数处理结果(0.删除失败;1.删除成功;2.已被使用不能删除;)
                paras[1] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[1].Direction = ParameterDirection.Output;

                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_Company_Delete", paras);

                retValue = int.Parse(paras[3].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_Company).FullName, "删除企业信息不成功", ex);

            }

            return retValue;
        }

        /// <summary>
        /// 添加企业
        /// </summary>
        /// <param name="company">企业</param>
        /// <returns>0.添加失败;1.添加成功;2.已经存在;</returns>
        public static int AddCompany(Company company)
        {
            // company = new Company();
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[4];

                //TB_Company【CompanyID,CompanyName_PS,IsUsing】
                //企业编号
                paras[0] = new SqlParameter("@in_CompanyID", string.IsNullOrEmpty(company.CompanyID) ? "" : company.CompanyID);
                //企业名称
                paras[1] = new SqlParameter("@in_CompanyName", string.IsNullOrEmpty(company.CompanyName) ? "" : company.CompanyName);
                //企业类型
                paras[2] = new SqlParameter("@in_CompanyType", company.CompanyType == null ? 1 : (int)company.CompanyType);


                //输入参数处理结果(0.添加失败;1.添加成功;2.已经存在;)
                paras[3] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[3].Direction = ParameterDirection.Output;


                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_Company_Add", paras);

                retValue = int.Parse(paras[3].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_Company).FullName, "添加企业信息不成功", ex);

            }

            return retValue;
        }



        /// <summary>
        /// 条件企业查询
        /// </summary>
        /// <param name="company">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<Company> GetCompanyByWhere(HZ.DrugTrade.Model.Query.Company company, int currentPage, int pageSize, out int totalRecord)
        {
            //switch (company.CompanyType)
            //{
            //    case Model.Enum.EnumCompanyType.生产企业:
            //        company.Append = "CompanyType = 0";
            //        break;
            //    case Model.Enum.EnumCompanyType.经营企业:
            //        company.Append = "CompanyType = 1";
            //        break;
            //    case Model.Enum.EnumCompanyType.配送企业:
            //        company.Append = "CompanyType = 2";
            //        break;
            //    case Model.Enum.EnumCompanyType.生产企业直接配送:
            //        company.Append = "CompanyType = 3";
            //        break;
            //    case Model.Enum.EnumCompanyType.医药物流企业:
            //        company.Append = "CompanyType = 4";
            //        break;
            //    case Model.Enum.EnumCompanyType.现代医药物流企业:
            //        company.Append = "CompanyType = 5";
            //        break;
            //    default:
            //        break;
            //}

            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.Company>(company, true);



            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM TB_Company WHERE 1=1 {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;
            DataTable dt = pdes.GetSpecailPage();
            IList<Company> list = HZ.DrugTrade.DAL2.DAL_Helper.GetCompaniesFromDataTable(dt);
            //IList<Company> list = HZ.DrugTrade.DAL2.DAL_Helper.CommonFillList<Company>(dt);
            return list;
        }

       
        /// <summary>
        /// 条件企业查询
        /// </summary>
        /// <param name="company">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<Company> GetCompanyByCondition(HZ.DrugTrade.Model.Query.Company company, int currentPage, int pageSize, out int totalRecord)
        {

            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.Company>(company, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT COMPANYID,COMPANYNAME,COMPANYTYPE,SNKEY FROM TB_Company INNER JOIN TB_ROLE_USER ON TB_COMPANY.COMPANYID=TB_ROLE_USER.USERID WHERE ROLEID='05' {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;
            DataTable dt = pdes.GetSpecailPage();
            IList<Company> list = HZ.DrugTrade.DAL2.DAL_Helper.GetCompaniesFromDataTable(dt);
            //IList<Company> list = HZ.DrugTrade.DAL2.DAL_Helper.CommonFillList<Company>(dt);
            return list;
        }

        /// <summary>
        /// 条件企业查询
        /// </summary>
        /// <param name="company">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<Company> GetCompanyByCondition_PS(HZ.DrugTrade.Model.Query.Company company, int currentPage, int pageSize, out int totalRecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.Company>(company, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT COMPANYID,COMPANYNAME,COMPANYTYPE,SNKEY FROM TB_Company INNER JOIN TB_ROLE_USER ON TB_COMPANY.COMPANYID=TB_ROLE_USER.USERID WHERE ROLEID='02' {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;
            DataTable dt = pdes.GetSpecailPage();
            IList<Company> list = HZ.DrugTrade.DAL2.DAL_Helper.GetCompaniesFromDataTable(dt);
            //IList<Company> list = HZ.DrugTrade.DAL2.DAL_Helper.CommonFillList<Company>(dt);
            return list;
        }

        /// <summary>
        /// 自助注册一个新企业信息
        /// </summary>
        /// <param name="companyinfo"></param>
        /// <returns></returns>
        public static int RegNewCompany(Model.CompanyInfo companyinfo)
        {


            //updated by sinodier 2011年1月12日21:58:43
            #region //updated by sinodier 2011年1月12日21:58:43
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();

                #region 企业已经存在
                string sqlCheck = @"select count(1) num FROM TB_USER WHERE UserID=@in_UserID or UserName = @in_CompanyName";

                List<string> parasnames1 = new List<string>();
                List<object> values1 = new List<object>();

                parasnames1.Add("@in_UserID");
                values1.Add(companyinfo.CompanyID);

                parasnames1.Add("@in_CompanyName");
                values1.Add(companyinfo.CompanyName);


                DataTable dt = h.ExecuteTable(CommandType.Text, sqlCheck, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());
                int count = int.Parse(dt.Rows[0]["num"].ToString());

                //企业已经存在
                if (count > 0)
                {
                    retValue = 2;

                }
                else
                {

                    string sqlInsertUser = @"Insert Into TB_User (UserID,UserName,Userpassword,isadmin,isUsing) values(@in_CompanyID,@in_CompanyName,'FE85E814FD656A2D490B842C6D33019D',1,0)";


                    string CompanyType = "";
                    if (companyinfo.CompanyID.ToLower().StartsWith("p"))
                        CompanyType = "2";
                    else if (companyinfo.CompanyID.ToLower().StartsWith("j"))
                        CompanyType = "1";
                    else if (companyinfo.CompanyID.ToLower().StartsWith("s"))
                        CompanyType = "0";

                    string sqlInsertCompany = @"Insert Into TB_Company Select @in_CompanyID,@in_CompanyName,@in_CompanyType,null from dual";

                    List<string> parasnames2 = new List<string>();
                    List<object> values2 = new List<object>();

                    parasnames2.Add("@in_CompanyID");
                    values2.Add(companyinfo.CompanyID);

                    parasnames2.Add("@in_CompanyName");
                    values2.Add(companyinfo.CompanyName);

                    h.ExecuteNonQuery(CommandType.Text, sqlInsertUser, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray());


                    parasnames2.Add("@in_CompanyType");
                    values2.Add(CompanyType);


                    h.ExecuteNonQuery(CommandType.Text, sqlInsertCompany, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray());


                    string sqlInsertRole_User = @"Insert Into Tb_Role_User Select @in_CompanyID,@in_CompanyType,1,@in_CompanyID from dual";

                    List<string> parasnames3 = new List<string>();
                    List<object> values3 = new List<object>();

                    parasnames3.Add("@in_CompanyID");
                    values3.Add(companyinfo.CompanyID);




                    parasnames3.Add("@in_CompanyType");
                    values3.Add(CompanyType == "2" ? "02" : "05");

                    //parasnames3.Add("@in_OrgID");
                    //values3.Add(companyinfo.CompanyID); 

                    h.ExecuteNonQuery(CommandType.Text, sqlInsertRole_User, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames3, values3).ToArray());

                    h.ExecuteNonQuery(HZ.Data.DAL_Helper.GetInsertSql(companyinfo));


                    // sb.Append(" Insert Into TB_User (UserID,UserName,Userpassword,isadmin,isUsing) values('" + companyinfo.CompanyID + "','" + companyinfo.CompanyName + "', '123456',1,0);");

                    //else
                    //{
                    //    sh.RollTrans();
                    //    return 2;
                    //}
                    //sb.Append(" Insert Into TB_Company Select '" + companyinfo.CompanyID + "','" + companyinfo.CompanyName + "'," + CompanyType + ",'';");
                    //sb.Append(" Insert Into Tb_Role_User Select '" + companyinfo.CompanyID + "','" + (CompanyType == "2" ? "02" : "05") + "',1,'" + companyinfo.CompanyID + "'; ");
                    //sb.Append(HZ.Data.DAL_Helper.GetInsertSql(companyinfo));



                    //sh.ExecuteNonQuery(sb.ToString());
                    retValue = 1;

                }
                #endregion

                h.CommitTrans();

            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("UpdateInvoice", ex.Message, ex);
                retValue = 0;
            }

            return retValue;


            #endregion

            //int flag = 0;
            //var sh = HZ.Data.DbHelperFactory.Create();
            //StringBuilder sb = new StringBuilder();
            //try
            //{
            //    sh.BeginTrans(); //锁定表
            //    // 插入医疗机构
            //    // 获取最大的UserID
            //    System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"\d+");
            //    //string sql = @"if exists(SELECT UserID FROM TB_USER WHERE UserID='"+companyinfo.CompanyID+@"') Select 1 
            //    //              Else if exists(select CompanyName from Tb_company where CompanyName like '%"+companyinfo.CompanyName+"%') Select 2 else Select 0";
            //    //string sqlCheck = string.Format("select count(*) FROM TB_USER WHERE UserID='{0}' or CompanyName like '%{1}%'", companyinfo.CompanyID, companyinfo.CompanyName);
            //    //object obj = sh.ExecuteScalar(sql);
            //    string maxid = string.Empty;


            //    string sqlCheck = @"select count(1) num FROM TB_USER WHERE UserID=@in_UserID or CompanyName = @in_CompanyName";




            //    int id = 0;
            //    //if (obj.ToString() == "1" || obj.ToString()=="2")
            //    //{
            //    //    sh.RollTrans();
            //    //    return 2;
            //    //}
            //    if (int.Parse(obj.ToString()) == 0)
            //    {


            //        sb.Append(" Insert Into TB_User (UserID,UserName,Userpassword,isadmin,isUsing) values('" + companyinfo.CompanyID + "','" + companyinfo.CompanyName + "', '123456',1,0);");
            //        string CompanyType = "";

            //        if (companyinfo.CompanyID.ToLower().StartsWith("p"))
            //            CompanyType = "2";
            //        else if (companyinfo.CompanyID.ToLower().StartsWith("j"))
            //            CompanyType = "1";
            //        else if (companyinfo.CompanyID.ToLower().StartsWith("s"))
            //            CompanyType = "0";
            //        else
            //        {
            //            sh.RollTrans();
            //            return 2;
            //        }
            //        sb.Append(" Insert Into TB_Company Select '" + companyinfo.CompanyID + "','" + companyinfo.CompanyName + "'," + CompanyType + ",'';");
            //        sb.Append(" Insert Into Tb_Role_User Select '" + companyinfo.CompanyID + "','" + (CompanyType == "2" ? "02" : "05") + "',1,'" + companyinfo.CompanyID + "'; ");
            //        sb.Append(HZ.Data.DAL_Helper.GetInsertSql(companyinfo));


            //        sh.ExecuteNonQuery(sb.ToString());
            //    }
            //    // 插入医疗机构帐号关联
            //    // 插入帐号权限
            //    sh.CommitTrans();
            //    return 1;
            //}
            //catch (Exception ee)
            //{
            //    sh.RollTrans();
            //    throw ee;
            //    return 0;
            //}
            //return 0;

        }

        /// <summary>
        /// 自助注册一个新企业信息 zdc 增加企业类型
        /// </summary>
        /// <param name="companyinfo"></param>
        /// <returns></returns>
        public static int RegNewCompany(Model.CompanyInfo companyinfo,string comType)
        {


            //updated by sinodier 2011年1月12日21:58:43
            #region //updated by sinodier 2011年1月12日21:58:43
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();

                #region 企业已经存在
                string sqlCheck = @"select count(1) num FROM TB_USER WHERE UserID=@in_UserID or UserName = @in_CompanyName";

                List<string> parasnames1 = new List<string>();
                List<object> values1 = new List<object>();

                parasnames1.Add("@in_UserID");
                values1.Add(companyinfo.CompanyID);

                parasnames1.Add("@in_CompanyName");
                values1.Add(companyinfo.CompanyName);


                DataTable dt = h.ExecuteTable(CommandType.Text, sqlCheck, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());
                int count = int.Parse(dt.Rows[0]["num"].ToString());

                //企业已经存在
                if (count > 0)
                {
                    retValue = 2;

                }
                else
                {

                    string sqlInsertUser = @"Insert Into TB_User (UserID,UserName,Userpassword,isadmin,isUsing) values(@in_CompanyID,@in_CompanyName,'E115DF884158EDDE618C3C637F3BD437',1,1)";


                    string CompanyType = "";
                    if (companyinfo.CompanyID.ToLower().StartsWith("qp"))
                        CompanyType = "2";
                    else if (companyinfo.CompanyID.ToLower().StartsWith("j"))
                        CompanyType = "1";
                    else if (companyinfo.CompanyID.ToLower().StartsWith("s"))
                        CompanyType = "0";

                    string sqlInsertCompany = @"Insert Into TB_Company Select @in_CompanyID,@in_CompanyName,@in_CompanyType,null,'','' from dual";

                    List<string> parasnames2 = new List<string>();
                    List<object> values2 = new List<object>();

                    parasnames2.Add("@in_CompanyID");
                    values2.Add(companyinfo.CompanyID);

                    parasnames2.Add("@in_CompanyName");
                    values2.Add(companyinfo.CompanyName);

                    h.ExecuteNonQuery(CommandType.Text, sqlInsertUser, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray());


                    parasnames2.Add("@in_CompanyType");
                    values2.Add(comType);


                    h.ExecuteNonQuery(CommandType.Text, sqlInsertCompany, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray());


                    string sqlInsertRole_User = @"Insert Into Tb_Role_User Select @in_CompanyID,@in_CompanyType,1,@in_CompanyID from dual";

                    List<string> parasnames3 = new List<string>();
                    List<object> values3 = new List<object>();

                    parasnames3.Add("@in_CompanyID");
                    values3.Add(companyinfo.CompanyID);

                    parasnames3.Add("@in_CompanyType");
                    values3.Add(CompanyType == "2" ? "02" : "05");

                    h.ExecuteNonQuery(CommandType.Text, sqlInsertRole_User, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames3, values3).ToArray());

                    h.ExecuteNonQuery(HZ.Data.DAL_Helper.GetInsertSql(companyinfo));
                    string sqlInsertTBSendPwd = string.Format(@"insert into tb_companysendpwd(userid,issend) values('{0}',0); 
                                                                insert into tb_userrule
                                                                SELECT '{0}',OperatingRuleID,1
                                                                  FROM View_RoleRule
                                                                 WHERE RoleID = '02' and module <> '子用户管理' and operatingruleid not in ('770','172','1921');", companyinfo.CompanyID);
                    h.ExecuteNonQuery("begin "+sqlInsertTBSendPwd+" end;");
                    retValue = 1;

                }
                #endregion

                h.CommitTrans();

            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("UpdateInvoice", ex.Message, ex);
                retValue = 0;
            }

            return retValue;


            #endregion

        }


        /// <summary>
        /// 条件企业查询
        /// </summary>
        /// <param name="company">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static DataTable GetCompanyInfoByWhere(HZ.DrugTrade.Model.Query.Company company, int currentPage, int pageSize, out int totalRecord)
        {
            switch (company.CompanyType)
            {
                case Model.Enum.EnumCompanyType.生产企业:
                    //company.Append = "CompanyType = '生产企业'";
                    company.Append = "CompanyType = 0";
                    break;
                case Model.Enum.EnumCompanyType.经营企业:
                    //company.Append = "CompanyType = '经营企业'";
                    company.Append = "CompanyType = 1";
                    break;
                case Model.Enum.EnumCompanyType.配送企业:
                    //company.Append = "CompanyType = '配送企业'";
                    company.Append = "CompanyType = 2";
                    break;
                case Model.Enum.EnumCompanyType.生产企业直接配送:
                    //company.Append = "CompanyType = '配送企业'";
                    company.Append = "CompanyType = 3";
                    break;
                case Model.Enum.EnumCompanyType.经营企业直接配送:
                    //company.Append = "CompanyType = '配送企业'";
                    company.Append = "CompanyType = 4";
                    break;
                default:
                    break;
            }

            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.Company>(company, true);
            //where = where.Replace("CompanyID", "COMPANYID");
            //            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format(@"SELECT TB_CompanyInfo.* , 
            //case CompanyType when 0 then '生产企业' 
            //when 1 then '经营企业' when 2 then '配送企业' end as CompanyType,case SNKEY when '2' then '已锁定' else '正常' end as SNKEY
            //  FROM TB_CompanyInfo inner join TB_Company on TB_Company.CompanyID=TB_CompanyInfo.CompanyID  WHERE 1=1 {0} ", where), pageSize);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format(@" select  
companyid, 
companyname, 
nvl(telno, 0) telno,
       nvl(cellno, 0) cellno,
       nvl(contactperson, ' ') contactperson,
       nvl(layman, ' ') layman,
       nvl(address, ' ') address,
       nvl(fax, ' ') fax,
       nvl(checkstatus,0) checkstatus,
      nvl(checkperson, ' ') checkperson,
      checkdate,
       nvl(totalfund,0) totalfund,
       nvl(fixfund,0) fixfund,
       nvl(website, ' ') website,
       nvl(email, ' ') email,
       nvl(isgsp, 0) isgsp,
       nvl(gspcode, ' ') gspcode,
        gsptime,
       nvl(remarks, ' ') remarks,
       nvl(businesscertificate,' ') businesscertificate,
        businesscertificatedate,
       nvl(businessrange, ' ') businessrange,
        addtime,
       nvl(addpersonid, ' ') addpersonid,
       nvl(author, ' ') author,
       nvl(authorcell, 0) authorcell,
       nvl(permission, ' ') permission,
       permissiondate,
       permissioncheckdate,
       nvl(storehouseaddress, ' ') storehouseaddress,
       nvl(adulterant, ' ') adulterant,
       nvl(comcode, ' ') comcode,
        comendtime,
       nvl(postcode, ' ') postcode,
       nvl(registaddress, ' ') registaddress,
       nvl(devious, ' ') devious,
       nvl(registerfund,0) registerfund,case CompanyType when 0 then '生产企业' 
when 1 then '经营企业' when 2 then '配送企业' when 3 then
          '生产企业直接配送'  when 4 then
          '经营企业直接配送'  end as CompanyType,case SNKEY when '2' then '已锁定' else '正常' end as SNKEY 
  from (select TB_CompanyInfo.*,CompanyType,SNKEY  FROM TB_CompanyInfo inner join TB_Company on TB_Company.CompanyID=TB_CompanyInfo.CompanyID ) where 1=1  {0} ", where), pageSize);



            //            string sql = string.Format(@"SELECT TB_CompanyInfo.* , 
            //case CompanyType when 0 then '生产企业' 
            //when 1 then '经营企业' when 2 then '配送企业' end as CompanyType,case SNKEY when '2' then '已锁定' else '正常' end as SNKEY
            //  FROM TB_CompanyInfo inner join TB_Company on TB_Company.CompanyID=TB_CompanyInfo.CompanyID  WHERE 1=1 {0} ", where);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            return pdes.GetSpecailPage();
        }
        /// <summary>
        /// 企业信息:含可配送区域
        /// </summary>
        /// <returns></returns>
        public static  DataTable GetCompanyInfoByWhere3(string strWhere, int currentPage, int pageSize, out int totalRecord)
        {
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format(@"select  
companyid, 
companyname, 
nvl(telno, 0) telno,
       nvl(cellno, 0) cellno,
       nvl(contactperson, ' ') contactperson,
       nvl(layman, ' ') layman,
       nvl(address, ' ') address,
       nvl(fax, ' ') fax,
       nvl(checkstatus,0) checkstatus,
      nvl(checkperson, ' ') checkperson,
      nvl(checkdate, null) checkdate,
       nvl(totalfund,0) totalfund,
       nvl(fixfund,0) fixfund,
       nvl(website, ' ') website,
       nvl(email, ' ') email,
       nvl(isgsp, 0) isgsp,
       nvl(gspcode, ' ') gspcode,
       nvl(gsptime, null) gsptime,
       nvl(remarks, ' ') remarks,
       nvl(businesscertificate,' ') businesscertificate,
       nvl(businesscertificatedate,null) businesscertificatedate,
       nvl(businessrange, ' ') businessrange,
       nvl(addtime,null) addtime,
       nvl(addpersonid, ' ') addpersonid,
       nvl(author, ' ') author,
       nvl(authorcell, 0) authorcell,
       nvl(permission, ' ') permission,
       nvl(permissiondate,null) permissiondate,
       nvl(permissioncheckdate,null) permissioncheckdate,
       nvl(storehouseaddress, ' ') storehouseaddress,
       nvl(adulterant, ' ') adulterant,
       nvl(comcode, ' ') comcode,
       nvl(comendtime,null) comendtime,
       nvl(postcode, ' ') postcode,
       nvl(registaddress, ' ') registaddress,
       nvl(devious, ' ') devious,
       nvl(registerfund,0) registerfund,case CompanyType when 0 then '生产企业' 
when 1 then '经营企业' when 2 then '配送企业' end as CompanyType,case SNKEY when '2' then '已锁定' else '正常' end as SNKEY 
  from (select TB_CompanyInfo.*,CompanyType,SNKEY  FROM TB_CompanyInfo inner join TB_Company on TB_Company.CompanyID=TB_CompanyInfo.CompanyID ) a 
  inner join TB_user on TB_user.userid=a.companyid  where CompanyID not in 
(SELECT CompanyID_TB FROM TB_SelectRelation) and  companyid not like 'QP%'"), pageSize);
            // HZ.Data.DbHelperFactory.Create().ExecuteTable(sql.ToString(),pageSize);
           pdes.CurrentPage = currentPage;
           totalRecord = pdes.TotalCount;

           return pdes.GetSpecailPage();

        }
        /// <summary>
        /// 获取所有未注册企业
        /// </summary>
        /// <returns>所有未注册企业</returns>
        public static IList<Company> GetAllUnregCompany()
        {
            string sql_GetAllUnregCompany = @"SELECT DISTINCT CompanyID, CompanyName, CompanyType FROM (
	SELECT     *
FROM         TB_Company LEFT OUTER JOIN
                      TB_Role_User ON TB_Company.CompanyID = TB_Role_User.OrgID AND TB_Role_User.IsAdmin = 1  AND  (TB_Role_User.RoleID = '02' or TB_Role_User.RoleID = '05')
) AS TT
WHERE TT.UserID IS NULL ";
            return HZ.Data.DAL_Helper.GetModelList<Company>(sql_GetAllUnregCompany, null);
        }
        /// <summary>
        /// 获取所有未注册企业
        /// </summary>
        /// <param name="blSc">是否为生产:true，生产企业；false，其它</param>
        /// <returns>所有未注册企业</returns>
        public static IList<Company> GetAllUnregCompany(bool blSc)
        {
            //删除as
            string sql_GetAllUnregCompany = @"SELECT DISTINCT CompanyID, CompanyName, CompanyType FROM (
	SELECT     *
FROM         TB_Company LEFT OUTER JOIN
                      TB_Role_User ON TB_Company.CompanyID = TB_Role_User.OrgID AND TB_Role_User.IsAdmin = 1  AND  (TB_Role_User.RoleID = '02' or TB_Role_User.RoleID = '05')
)  TT
WHERE TT.UserID IS NULL ";
            if (blSc)
            {
                sql_GetAllUnregCompany += "and CompanyType=0  ";
            }
            else
            {
                sql_GetAllUnregCompany += "and CompanyType<>0  ";
            }
            return HZ.Data.DAL_Helper.GetModelList<Company>(sql_GetAllUnregCompany, null);
        }

        public static int MarkLinkToCompanyInfo(CompanyInfo companyinfo, Company company)
        {
            string sql = string.Format("UPDATE TB_CompanyInfo SET CompanyID = '{0}' WHERE CompanyName = '{1}'", company.CompanyID, companyinfo.CompanyName);
            return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
            throw new NotImplementedException();
        }

        public static IList<CompanyInfo> GetUnregedCompanyInfo(HZ.DrugTrade.Model.Query.CompanyInfo where, int currentpage, int pagesize, out int totalrecord)
        {

            string sql = string.Format("SELECT * FROM TB_CompanyInfo WHERE CompanyID IS NULL {0}", HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where, true));

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;


            return HZ.Data.DAL_Helper.CommonFillList<CompanyInfo>(des.GetSpecailPage());

            throw new NotImplementedException();
        }

        public static int AduitCompany(IList<CompanyInfo> list, out string outUserIDs)
        {
            var sh = HZ.Data.DbHelperFactory.Create();
            outUserIDs = "";
            try
            {
                sh.BeginTrans(); //锁定表
                // 插入医疗机构
                System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"\d+");
                string sql = "SELECT MAX(UserID) FROM TB_USER WHERE UserID LIKE 'P%'";
                object obj = sh.ExecuteScalar(sql);
                string maxid = string.Empty;
                int id = 0;
                if (Convert.IsDBNull(obj))
                {
                    maxid = "P0001";
                }
                else
                {
                    maxid = obj.ToString();
                    id = int.Parse(regex.Match(maxid).Value);
                }
                // 获取医疗机构ID
                sql = "SELECT MAX(CompanyID) FROM TB_Company Where CompanyId LIKE 'P%' ";
                obj = sh.ExecuteScalar(sql);
                string maxhospitalid = "";
                int hid = 0;

                if (Convert.IsDBNull(obj))
                {
                    maxhospitalid = "P0001";
                }
                else
                {
                    maxhospitalid = obj.ToString();
                    hid = int.Parse(regex.Match(maxhospitalid).Value);
                }
                // 插入帐号
                for (int i = 0; i < list.Count; i++)
                {
                    id++;
                    hid++;
                    // 插入医疗机构

                    string companyid = "P" + hid.ToString("D4");
                    string userid = "P" + id.ToString("D4");
                    outUserIDs += list[i].CompanyName + "," + userid + ";";
                    Model.Company company = new Company()
                    {
                        CompanyID = companyid,
                        CompanyName = list[i].CompanyName,
                        // CompanyType = Model.Enum.EnumCompanyType.配送企业

                    };
                    //sql = HZ.Data.DAL_Helper.GetInsertSql(company);
                    //sh.ExecuteNonQuery(sql);
                    HZ.Data.DAL_Helper.OperateTableSQL<Model.Company>(company, HZ.Data.TableOperator.Insert, sh);
                    Model.User_Tail user = new User_Tail()
                    {
                        UserID = userid,
                        IsAdmin = true,
                        IsUsing = true,
                        UserPassword = HZ.Fun2.GetMd5("999999"),
                        UserName = company.CompanyName
                    };
                    //sql = HZ.Data.DAL_Helper.GetInsertSql(user);
                    //sh.ExecuteNonQuery(sql);
                    HZ.Data.DAL_Helper.OperateTableSQL<Model.User_Tail>(user, HZ.Data.TableOperator.Insert, sh);
                    // 更新关联
                    sql = string.Format("UPDATE TB_CompanyInfo SET CompanyID = '{0}' WHERE CompanyName = '{1}' ", companyid, list[i].CompanyName);
                    sh.ExecuteNonQuery(sql);


                    // 插入用户表机构关联

                    Model.Role_User ru = new Role_User()
                    {
                        IsAdmin = true,
                        OrgID = companyid,
                        RoleID = "02",
                        UserID = userid
                    };
                    //sql = HZ.Data.DAL_Helper.GetInsertSql(ru);
                    //sh.ExecuteNonQuery(sql);
                    HZ.Data.DAL_Helper.OperateTableSQL<Model.Role_User>(ru, HZ.Data.TableOperator.Insert, sh);
                    // 插入用户权限

                    sql = string.Format(@"INSERT INTO TB_UserRule (UserID,OperatingRuleID,IsEffective)
SELECT '{0}', OperatingRuleID ,1 FROM TB_Role_Rule WHERE RoleID = '02' AND IsUsing = 1", userid);
                    sh.ExecuteNonQuery(sql);
                }


                // 插入医疗机构帐号关联
                // 插入帐号权限
                sh.CommitTrans();
                return 1;
            }
            catch (Exception ee)
            {
                sh.RollTrans();
                throw ee;
            }
            return 0;
            throw new NotImplementedException();
            throw new NotImplementedException();
        }

        public static int DeleteCompanyInfo(IList<CompanyInfo> list)
        {
            var sh = HZ.Data.DbHelperFactory.Create();
            try
            {
                sh.BeginTrans();
                foreach (var item in list)
                {
                    string sql = string.Format("DELETE FROM TB_CompanyInfo WHERE CompanyName = '{0}'", item.CompanyName);
                    sh.ExecuteNonQuery(sql);

                }
                sh.CommitTrans();
                return 1;

            }
            catch (Exception ee)
            {
                sh.RollTrans();
                return 0;
            }

        }

        /// <summary>
        /// 根据企业ID获取企业其它信息
        /// </summary>
        /// <param name="companyid"></param>
        /// <returns></returns>
        public static CompanyInfo GetCompanyInfoByID(string companyid)
        {
            try
            {
                //改版
                //string sql = string.Format("SELECT TOP 1 * FROM TB_CompanyInfo WHERE CompanyID = '{0}'", companyid);
                string sql = string.Format("select * from (SELECT a.*,b.companytype FROM TB_CompanyInfo a inner join tb_company b on a.companyid = b.companyid) WHERE CompanyID = '{0}' and rownum<=1", companyid);
                return HZ.Data.DAL_Helper.CommonFill<CompanyInfo>(HZ.Data.DbHelperFactory.Create().ExecuteTable(sql).Rows[0]);
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="lst"></param>
        /// <returns></returns>
        public static int UpdateCompanySNKEY(IList<Model.Company> lst)
        {
            var h = HZ.Data.DbHelperFactory.Create();

            try
            {
                h.BeginTrans();
                string strSql = string.Empty;
                foreach (Model.Company model in lst)
                {
                    strSql = string.Format(@"UPDATE TB_Company  SET SNKEY ='{0}' WHERE CompanyID = '{1}';", model.SNKEY, model.CompanyID);

                    h.ExecuteNonQuery(strSql);
                }

                h.CommitTrans();
                return 1;
            }
            catch (Exception ee)
            {
                h.RollTrans();
                HZ.Common.log4netManager.LogDebug("UpdateCompanySNKEY", ee.Message, ee);
                return 0;
            }
        }


        /// <summary>
        /// 获取符合条件的企业
        /// </summary>
        /// <returns>获取符合条件的企业</returns>
        public static IList<Company> GetCompanyByWhere(string strWhere)
        {
            string sql_GetAllCompany = string.Format(@"SELECT * FROM TB_Company  {0}", strWhere);
            return HZ.Data.DAL_Helper.GetModelList<Company>(sql_GetAllCompany, null);
        }

        /// <summary>
        /// 获取所有国内生产企业和配送企业信息由生产企业勾选用
        /// </summary>
        /// <returns></returns>
        public static DataTable GetCompanyInfo()
        {
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable("select * from View_CompanyUsers");
            return dt;
        }
        /// <summary>
        /// 中心审核配送企业的配送区域 zdc
        /// </summary>
        /// <param name="ps"></param>
        /// <param name="checkUser">审核人</param>
        /// <param name="areaIDs">审核区域</param>
        /// <param name="checkType">0:审核未通过，否则通过</param>
        /// <returns></returns>
        public static bool CheckAreaByZbzx(PSSelectArea ps, string checkUser, string areaIDs, int checkType)
        {
            bool flag = false;
            try
            {
                string updateInfo = "";
                if (checkType!=0)//审核通过
                {
                    updateInfo = string.Format("update TB_PSSelectArea set checkByZbzx=1,checkUserByZbzx='{0}',checkTimeByZbzx=sysdate where companyPS_Id='{1}' and areaid in ({2}) ;", checkUser, ps.CompanyPS_Id, areaIDs);
                }
                else//审核未通过
                {
                    updateInfo = string.Format("update TB_PSSelectArea set checkByZbzx=2,checkUserByZbzx='{0}',reasonByZbzx='{3}',checkTimeByZbzx=sysdate where companyPS_Id='{1}' and areaid in ({2}) ;", checkUser, ps.CompanyPS_Id, areaIDs,ps.ReasonByZbzx);
                }
                int count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery("  begin   "+updateInfo+"  end;    ");
                if (count>0)
                {
                    flag = true;
                }
                return flag;
            }
            catch (Exception)
            {
                return flag;
                throw;
            }
        }
        /// <summary>
        /// 审核配送企业的详细信息 zdc
        /// </summary>
        /// <param name="ps"></param>
        /// <param name="checkType"></param>
        /// <param name="checkUser"></param>
        /// <returns></returns>
        public static bool CheckCompanyInfo(PSSelectArea ps, int checkType, string checkUser,string state)
        {
            bool flag = false;
            try
            {
                string updateInfo = "";
                if (state == "1")
                {
                    if (checkType == 1)//审核通过
                    {
                        updateInfo = string.Format("update TB_AreaCompany_PS set checkstatus=1,checkPerson='{0}',checkdate=sysdate where companyid='{1}' and areaid = '{2}';", checkUser, ps.CompanyPS_Id,ps.AreaId);
                    }
                    else//审核未通过
                    {
                        updateInfo = string.Format("update TB_AreaCompany_PS set checkstatus=2,checkPerson='{0}',checkReason='{1}',checkdate=sysdate where companyid='{2}'  and areaid = '{3}';", checkUser, ps.Reason, ps.CompanyPS_Id,ps.AreaId);
                    }
                }
                else
                {
                    if (checkType == 1)//审核通过
                    {
                        updateInfo = string.Format("update TB_CompanyInfo set checkstatus=1,checkPerson='{0}',checkdate=sysdate where companyid='{1}';", checkUser, ps.CompanyPS_Id);
                    }
                    else//审核未通过
                    {
                        updateInfo = string.Format("update TB_CompanyInfo set checkstatus=2,checkPerson='{0}',checkReason='{1}',checkdate=sysdate where companyid='{2}';", checkUser, ps.Reason, ps.CompanyPS_Id);
                    }
                }
                int count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery("  begin   " + updateInfo + "  end;    ");
                if (count>0)
                {
                    flag = true;
                }
                return flag;
            }
            catch (Exception)
            {
                return flag;
                throw;
            }
        }
        /// <summary>
        /// 审核配送企业和配送区域的权利 zdc
        /// </summary>
        /// <param name="ps"></param>
        /// <param name="checkType"></param>
        /// <param name="checkUser"></param>
        /// <returns></returns>
        public static bool CheckEnterpriseInfo(PSSelectArea ps, int checkType, string checkUser)
        {
            bool flag = false;
            try
            {
                string updateInfo = "";
                if (checkType == 1)//审核通过
                {
                    if (checkUser.ToUpper().Contains("ZBZX"))//中心审核
                    {
                        updateInfo = string.Format("update TB_PSSelectArea set checkStatus=1,checkUser='{0}',checkTime=sysdate where companyPS_Id='{1}' and areaid='{2}' ;", checkUser, ps.CompanyPS_Id,ps.AreaId);
                    }
                    else//盟市审核
                    {
                        updateInfo = string.Format("update TB_PSSelectArea set checkStatus=1,checkUser='{0}',checkTime=sysdate where companyPS_Id='{1}' and areaId =(select AreaID from TB_ManageOrg where ManageOrgID='{2}' ) ;", checkUser, ps.CompanyPS_Id, checkUser);
                    }

                }
                else//审核不通过
                {
                    if (checkUser.ToUpper().Contains("ZBZX"))//中心审核
                    {
                        updateInfo = string.Format("update TB_PSSelectArea set checkStatus=2,checkUser='{0}',reason='{1}',checkTime=sysdate where companyPS_Id='{2}'  and areaid='{3}' ;", checkUser, ps.ReasonByZbzx, ps.CompanyPS_Id,ps.AreaId);
                    }
                    else//盟市审核
                    {
                        updateInfo = string.Format("update TB_PSSelectArea set checkStatus=2,checkUser='{0}',reason='{1}',checkTime=sysdate where companyPS_Id='{2}' and areaId =(select AreaID from TB_ManageOrg where ManageOrgID='{3}' ) ;", checkUser, ps.Reason, ps.CompanyPS_Id, checkUser);
                    }
                }
                if (!string.IsNullOrEmpty(updateInfo))
                {
                    
                    int count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery("  begin   "+updateInfo+"  end; ");
                    if (count > 0)
                    {
                        flag = true;
                    }
                }


            }
            catch (Exception)
            {

                throw;
            }
            return flag;

        }
        /// <summary>
        /// 获取配送企业选择已提交的配送区域 zdc 
        /// </summary>
        /// <param name="comPS_Id"></param>
        /// <returns></returns>
        public static DataTable GetAreasById(string comPS_Id)
        {
            DataTable dt = new DataTable();
            try
            {
                string sql = string.Format(@"select * from (select a.*,case a.checkByZbzx when 1 then '审核通过' when 2 then '审核未通过' else '未审核' end as checkByZbzxdc,b.contactperson,b.cellno,b.address from View_SelectAreaManage a 
                            inner join tb_companyinfo b on a.companyPS_Id = b.companyid) where 1=1 {0} and submitstatus=1 order by areaid", comPS_Id);
                dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
            }
            catch (Exception)
            {
                return dt;
                throw;
            }
            
            return dt;
        }
        /// <summary>
        /// 获取配送企业选择的配送区域 zdc 
        /// </summary>
        /// <param name="comPS_Id"></param>
        /// <returns></returns>
        public static string GetAreasByComId(string comPS_Id)
        {
            string areas = "";
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable("select * from TB_Area where areaId in (select areaId from TB_PSselectArea where companyPS_id ='" + comPS_Id + "')");
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    areas += dt.Rows[i]["areaName"] + ",";
                }
            }
            return areas.Substring(0, areas.Length - 1);
        }
        /// <summary>
        /// 获取配送企业的类型 zdc
        /// </summary>
        /// <param name="comPS_Id"></param>
        /// <returns></returns>
        public static string GetCompanyType(string comPS_Id)
        {
            string comType = "";
            string sqlInfo = string.Format("select CompanyType from TB_Company where CompanyID='{0}'", comPS_Id);
            string type = HZ.Data.DbHelperFactory.Create().ExecuteScalar(sqlInfo).ToString();
            switch (type)
            {
                case "1":
                    comType = "经营企业";
                    break;
                case "2":
                    comType = "配送企业";
                    break;
                case "3":
                    comType = "生产企业直接配送";
                    break;
                case "4":
                    comType = "经营企业直接配送";
                    break;
                //case "5":
                //    comType = "现代医药物流企业";
                //    break;
                default:
                    break;
            }
            return comType;
        }
        /// <summary>
        /// 配送企业查看审核原因 zdc
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="areaId"></param>
        /// <returns></returns>
        public static string GetReason(string userId, string areaId)
        {
            string reason = "";
            try
            {
                string sqlzbzx;
                if (userId.ToUpper().Contains("ZBZX"))//若是中心查看
                {
                    sqlzbzx = string.Format("select Reason,ReasonByZbzx,CheckStatus,CheckByZbzx from TB_PSSelectArea where CheckUserByZbzx='{0}' and AreaID='{1}'", userId, areaId);
                }
                else
                {
                    sqlzbzx = string.Format("select Reason,ReasonByZbzx,CheckStatus,CheckByZbzx from TB_PSSelectArea where CompanyPS_Id='{0}' and AreaID='{1}'", userId, areaId);
                }
                DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sqlzbzx);
                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0]["CheckStatus"].ToString() == "2")//盟市审核不通过
                    {
                        reason += "盟市审核原因：" + dt.Rows[0]["reason"].ToString() + "\n";
                    }
                    if (dt.Rows[0]["CheckByZbzx"].ToString() == "2")//中心审核不通过
                    {
                        reason += "中心审核原因：" + dt.Rows[0]["reasonByZbzx"].ToString();
                    }
                }
                if (reason == "")
                {
                    return "";
                }
                else
                {
                    return reason;
                }
            }
            catch (Exception)
            {
                return reason;
                throw;
            }

        }
        /// <summary>
        /// 获取企业类型 add zdc
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static string GetComType(string userId)
        {
            string companyType = "";
            string sql = string.Format("select CompanyType from TB_Company where CompanyID='{0}'", userId);
            string result = HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql).ToString();
            switch (result)
            {
                case "0":
                    companyType = "生产企业";
                    break;
                case "1":
                    companyType = "经营企业";
                    break;
                case "2":
                    companyType = "配送企业";
                    break;
                case "3":
                    companyType = "生产企业直接配送";
                    break;
                case "4":
                    companyType = "经营企业直接配送";
                    break;
                //case "5":
                //    companyType = "现代医药物流企业";
                //    break;
                default:
                    break;
            }
            return companyType;
        }
        /// <summary>
        /// 获取要发放密码的企业列表 add by zdc
        /// </summary>
        /// <param name="strWhere"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static IList<Model.View.CompanySendPwd> GetCompanySendByWhere(string strWhere, int currentPage, int pageSize, out int totalRecord)
        {
                string sql = string.Empty;
                sql = string.Format("select * from View_CompanySendPwd where 1=1 {0}", strWhere);
                HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql.ToString(), pageSize);
                des.CurrentPage = currentPage;
                totalRecord = des.TotalCount;
                return HZ.Data.DAL_Helper.CommonFillList<Model.View.CompanySendPwd>(des.GetSpecailPage());
            
        }

        /// <summary>
        /// 获取企业用户信息 add by zdc
        /// </summary>
        /// <param name="comId"></param>
        /// <returns></returns>
        public static Model.View.CompanySendPwd GetCom(string comId)
        {
            Model.View.CompanySendPwd csp = new HZ.DrugTrade.Model.View.CompanySendPwd();
            csp.CompanyId = comId;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable("select * from View_CompanySendPwd where CompanyId='" + comId + "'");
            csp.Author = dt.Rows[0]["Author"].ToString();
            csp.AuthorCell = dt.Rows[0]["AuthorCell"].ToString();
            csp.CellNo = dt.Rows[0]["CellNo"].ToString();
            csp.CompanyName = dt.Rows[0]["CompanyName"].ToString();
            csp.CompanyType = int.Parse(dt.Rows[0]["CompanyType"].ToString());
            csp.ContactPerson = dt.Rows[0]["ContactPerson"].ToString();
            csp.IsSend = int.Parse(dt.Rows[0]["IsSend"].ToString());
            return csp;
        }
        /// <summary>
        /// 发放企业用户密码 add by zdc
        /// </summary>
        /// <param name="ComAccountPwd">企业用户</param>
        /// <param name="outComAccountPwd">企业用户</param>
        /// <returns>0.失败；1成功</returns>
        public static int SendComAccountPWD(Model.View.CompanySendPwd companySendPwd, out Model.View.CompanySendPwd outCompanySendPwd)
        {
            int intFlag = 0;
            companySendPwd.UserPwd = Guid.NewGuid().ToString().ToLower().Substring(0, 6);
            companySendPwd.SendDate =System.DateTime.Now;
            HZ.Data.IDbHelper db = HZ.Data.DbHelperFactory.Create();
            try
            {
                string strUpdateAccount = string.Format(@"UPDATE tb_user SET userpassword='{0}' WHERE userid='{1}';
                                                        update tb_companysendpwd set issend=1,sendoperator='{2}',senddate=sysdate,userpwd='{4}' where userid='{1}'; 
                                                        ", HZ.Fun.GetMd5(companySendPwd.UserPwd),companySendPwd.CompanyId,
                                                         companySendPwd.SendOperator,System.DateTime.Now.ToString(),companySendPwd.UserPwd);
               int count= db.ExecuteNonQuery("begin "+strUpdateAccount+" end;");
               if (count>0)
               {
                intFlag = 1;
               }

            }
            catch (Exception ex)
            {
                db.RollTrans();

            }
            outCompanySendPwd = companySendPwd;
            return intFlag;
        }
        /// <summary>
        /// 返回公司的文件过期情况
        /// </summary>
        /// <param name="companyid">公司ID</param>>
        /// <returns></returns>
        public static DataTable GetCmpTimeinfo(string companyid, string companyname)
        {
            string sql = "";
            DataTable dt = null;
            sql = "select companyname,COMPANYID,case when permissiondate is null then -2 when permissiondate-sysdate<0  then -1 when permissiondate-sysdate>90  then 91 when permissiondate-sysdate<90 and permissiondate-sysdate>0  then trunc(permissiondate-sysdate) end as 营业,case when businesscertificatedate is null then -2 when businesscertificatedate-sysdate<0  then -1 when businesscertificatedate-sysdate>90  then 91 when businesscertificatedate-sysdate<90 and businesscertificatedate-sysdate>0  then trunc(businesscertificatedate-sysdate) end as 许可,case when comendtime is null then -2 when comendtime-sysdate<0  then -1 when comendtime-sysdate>90  then 91 when comendtime-sysdate<90 and comendtime-sysdate>0  then trunc(comendtime-sysdate) end as 组织,case when permissioncheckdate is null then -2 when permissioncheckdate-sysdate+365<0  then -1 when permissioncheckdate-sysdate+365>90  then 91 when permissioncheckdate-sysdate+365<90 and permissioncheckdate-sysdate+365>0  then trunc(permissioncheckdate-sysdate+365) end as 年检 from Tb_Companyinfo where 1=1";
            if (companyid != "")
            {
                sql += "   and companyid in(" + companyid + ")";
            }

            if (companyname != "")
            {
                sql +=" and companyname like '%" + companyname + "%'"; 
            }
           
            try
            {
                dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
            }
            catch
            {
                return dt;
            }
            return dt;
        }

        /// <summary>
        /// 分页先是企业信息 add by gaoyang
        /// </summary>
        /// <param name="companyid">企业流水号</param>
        /// <param name="companyname">企业名称</param>
        /// <param name="currentPage">当前是第几页</param>
        /// <param name="pageSize">每页显示的条目数</param>
        /// <param name="totalRecord">一共分多少页</param>
        /// <returns></returns>
        public static IList<Model.View.VCompanyInfo> GetCmpTimeinfoByWhere(string companyid, string companyname, int currentPage, int pageSize, out int totalRecord)
        {
            string sql = "select companyname,COMPANYID,case when permissiondate is null then -2 when permissiondate-sysdate<0  then -1 when permissiondate-sysdate>90 then 91 when permissiondate-sysdate<90 and permissiondate-sysdate>0  then trunc(permissiondate-sysdate) end as permissiondate,case when businesscertificatedate is null then -2 when businesscertificatedate-sysdate<0  then -1 when businesscertificatedate-sysdate>90  then 91 when businesscertificatedate-sysdate<90 and businesscertificatedate-sysdate>0  then trunc(businesscertificatedate-sysdate) end as businesscertificatedate,case when comendtime is null then -2 when comendtime-sysdate<0  then -1 when comendtime-sysdate>90  then 91 when comendtime-sysdate<90 and comendtime-sysdate>0  then trunc(comendtime-sysdate) end as comendtime,case when permissioncheckdate is null then -2 when permissioncheckdate-sysdate+365<0  then -1 when permissioncheckdate-sysdate+365>90  then 91 when permissioncheckdate-sysdate+365<90 and permissioncheckdate-sysdate+365>0  then trunc(permissioncheckdate-sysdate+365) end as permissioncheckdate from Tb_Companyinfo where 1=1 and companyid not in (select companyid from tb_company where snkey='2')";
            if (companyid != "")
            {
                sql += "  and companyid in(" + companyid + ")";
            }

            if (companyname != "")
            {
                sql += " and companyname like '%" + companyname + "%'";
            }
            sql += " and (companyid like 'QS%' or companyid like 'QJ%') and companyid in (select userid from tb_user where (userid like 'QS%' or userid like 'QJ%') and isusing = 1) Order by COMPANYID";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql.ToString(), pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<Model.View.VCompanyInfo>(des.GetSpecailPage());
        }
        /// <summary>
        /// 根据企业类型得到一共有多少条数据
        /// </summary>
        /// <param name="roleId">tb_role_user表的roleId用来判断用户类型</param>
        //public static int GetCompanyCount(string roleId)
        //{
        //    int i=0;
        //    string sql = "SELECT COUNT(*) FROM tb_role_user WHERE ROLEID='" + roleId + "'";
        //    IDataReader reader = HZ.Data.DbHelperFactory.Create().ExecuteReader(sql);
        //    if (reader.Read())
        //    {
        //        i = reader.GetInt32(0);
        //    }
        //    return i;
        //}
        /// <summary>
        /// 更新企业信息
        /// add by Meng 2013-03-20
        /// </summary>
        /// <param name="companyinfo"></param>
        /// <returns></returns>
        public static int UpdateCompanyInfoByCompanyId(HZ.DrugTrade.Model.CompanyInfo  companyinfo)
        { 
            string strsql = string.Format(@"begin update Tb_CompanyInfo set CompanyName = '{0}',TelNo='{1}',ContactPerson = '{2}',CellNo='{3}',LayMan='{4}',
                                            Address = '{5}',BusinessCertificate='{6}',Fax='{7}',Website='{8}',BusinessRange='{9}',
                                            Remarks = '{10}' where companyid = '{11}';update tb_company set CompanyName = '{0}' where companyid = '{11}';
                                            update tb_user set username = '{0}' where userid = '{11}';end;", companyinfo.CompanyName, companyinfo.TelNo, companyinfo.ContactPerson, companyinfo.CellNo,
                                                                                       companyinfo.LayMan,companyinfo.Address,companyinfo.BusinessCertificate,
                                                                                       companyinfo.Fax,companyinfo.Website,companyinfo.BusinessRange,companyinfo.Remarks,companyinfo.CompanyID);
            return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(strsql);
        }
    }
}
